import mysql.connector
import cv2
import numpy as np
from datetime import datetime

def display_detection_data():
    """从数据库读取检测数据并可视化展示"""
    # 数据库配置（需要根据实际情况修改）
    db_config = {
        "host": "localhost",
        "user": "root",
        "password": "LiuJiaCheng666",
        "database": "image_db"
    }

    try:
        # 建立数据库连接
        conn = mysql.connector.connect(**db_config)
        cursor = conn.cursor()

        # 执行查询
        query = "SELECT time, data, classid, ispollination FROM images ORDER BY time DESC"
        cursor.execute(query)
        results = cursor.fetchall()

        if not results:
            print("数据库中没有检测记录")
            return

        print(f"找到 {len(results)} 条检测记录")
        print("-" * 60)

        # 遍历结果
        for idx, (db_time, image_data, class_id, is_pollination) in enumerate(results, 1):
            # 转换二进制数据为numpy数组
            nparr = np.frombuffer(image_data, np.uint8)
            img = cv2.imdecode(nparr, cv2.IMREAD_COLOR)

            if img is None:
                print(f"记录 {idx} 图像解码失败")
                continue

            # 格式化输出
            time_str = db_time.strftime("%Y-%m-%d %H:%M:%S")
            output = f"""
            记录编号: {idx}
            检测时间: {time_str}
            类别ID: {class_id}
            授粉判断: {'需要授粉' if is_pollination else '无需操作'}
            图像尺寸: {img.shape[1]}x{img.shape[0]}
            """
            print(output)
            print("-" * 60)

            # 显示图像
            cv2.imshow(f"检测记录 {idx} - 类别 {class_id}", img)
            cv2.waitKey(0)  # 按任意键继续
            cv2.destroyAllWindows()

    except mysql.connector.Error as err:
        print(f"数据库错误: {err}")
    except Exception as e:
        print(f"运行时错误: {e}")
    finally:
        if 'conn' in locals() and conn.is_connected():
            cursor.close()
            conn.close()
            print("数据库连接已关闭")

# 使用示例
if __name__ == "__main__":
    display_detection_data()